package com.iworkh.spring.data.jdbc;

import com.iworkh.spring.data.jdbc.dao.Car;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * jdbc测试类
 *
 * @author: iworkh-沐雨云楼
 * @date: 2019-12-19
 */
public class JdbcTemplateTest {

    public static void main(String[] args) {

        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/iworkh_spring?characterEncoding=utf8&useSSL=true");

        dataSource.setUsername("iworkh");
        dataSource.setPassword("iworkh123");
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        // 创建表
        // jdbcTemplate.execute("create table t_iworkh_car(id int primary key, name varchar(32), price int)");

        // 插入数据
        jdbcTemplate.update("insert into t_iworkh_car(id,name,price) values (1, 'bwm', 50)");


        // String[] sqls = {"insert into t_iworkh_car(id,name,price) values (2,'audi',20)",
        //                  "insert into t_iworkh_car(id,name,price) values (3,'ben',40)"};
        //
        // jdbcTemplate.batchUpdate(sqls);

        // 更新
        jdbcTemplate.update("update t_iworkh_car set price=38 where id = 3 ");

        // 删除
        jdbcTemplate.update("delete from t_iworkh_car where id = 1 ");

        // 查询
        Integer integer = jdbcTemplate.queryForObject("select count(*) from t_iworkh_car", Integer.class);
        System.out.println("count: " + integer);

        RowMapper<Car> rowMapper = new BeanPropertyRowMapper<>(Car.class);

        // RowMapper<Car> rowMapper = (rs, rowNum) -> {
        //     Car car = new Car();
        //     car.setId(rs.getString("id"));
        //     car.setName(rs.getString("name"));
        //     car.setPrice(rs.getInt("price"));
        //     return car;
        // };

        Car car = jdbcTemplate.queryForObject("select * from t_iworkh_car where id=? ", rowMapper,"2");
        System.out.println("queryForObject: " + car);

        Map<String, Object> carMap = jdbcTemplate.queryForMap("select * from t_iworkh_car where id=?","3");
        System.out.println("queryForMap: " + carMap);


        // 注意：这返回的是Integer，String等单列的list，而不是多属性的对象的list
        List<String> nameList = jdbcTemplate.queryForList("select name from t_iworkh_car", String.class);
        System.out.println("queryForList_baseObjectList: " + nameList);


        List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from t_iworkh_car");
        System.out.println("queryForList_mapList: " + mapList);


        List<Car> carList = jdbcTemplate.query("select * from t_iworkh_car", rowMapper);
        System.out.println("query: "+carList);

        System.out.println("end...");

    }
}
